The performance of a database system can depend largely on its physical database design. A database comprises a set of tables of data along with information about relations between the tables. Tables represent relations over the data. Each table comprises a set of records of data stored in one or more data fields. The records of a table are also referred to as rows, and the data fields of records in a table are also referred to as columns.
A database management system (e.g., a relational database management system (RDBMS)), which provides access to the data in a database, processes data manipulation statements or queries. Such statements and queries are processed, for example, to retrieve, update, insert, and/or delete data in a database. Queries are defined by a query language supported by the database management system (which may be referred to simply as a “database system” or “database server”). For example, users can write queries and run them against a database to retrieve desired information from the database. In general, to retrieve requested information, a database server can retrieve an appropriate table from storage, load it into memory, and search row by row for the desired information. However, given the substantial size of most databases, searching for data in this manner is often time-consuming and inefficient.
To enhance performance in processing queries, database servers use indexes to help access data in a database more efficiently. Although all possible indexes for a database can be constructed, the number of possible indexes over a database can be very large and can significantly increase the memory requirements, including storage requirements, for the database, as well as the cost associated with keeping the indexes up-to-date as data is updated, inserted, and deleted. Also, many of the possible indexes may provide no or minimal performance advantage considering the data in the database, the organization of the data in the database, and the usage of the database as represented by a workload of queries executed against the database. Typical database systems therefore use only a subset of possible indexes for accessing databases.
A database administrator (DBA) typically selects a set of indexes for use by a database system from among possible combinations of indexes for a database. The effectiveness of any set of indexes depends, for example, on the data in the database, on the query optimizer selecting execution plans for the queries submitted by the users, and on the workload of queries to be executed against the database. As a database system evolves through database updates and modifications and through changes in workloads of queries, for example, new sets of indexes should be considered and selected to help maintain a desired level of performance of the database system.
Tuning the physical design of a database management system, which includes, among other things, selecting the optimal set of indexes, materialized views, and partitioning, is critical to achieve good application performance. However, the variety in choice of physical design structures makes the task of tuning a database system (database server) quite daunting. The selection of which physical design structure to create is also challenging because the creation of different physical design structures can have very different storage and update consequences (e.g., how attributes of the physical design are affected when data is updated, inserted, or deleted).